﻿CREATE SCHEMA [Content]
    AUTHORIZATION [dbo];


GO
CREATE TABLE [Content].[HtmlContent] (
    [ContentId]      UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [DomainId]       UNIQUEIDENTIFIER NOT NULL,
    [ContentText]    NVARCHAR (MAX)   NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Content].[BlogAuthors] (
    [BlogAuthorId] UNIQUEIDENTIFIER NOT NULL,
    [BlogId]       UNIQUEIDENTIFIER NOT NULL,
    [AuthorId]     UNIQUEIDENTIFIER NOT NULL,
    [AuthorName]   NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Content].[BlogPost] (
    [BlogPostId]     UNIQUEIDENTIFIER NOT NULL,
    [BlogId]         UNIQUEIDENTIFIER NOT NULL,
    [AuthorId]       UNIQUEIDENTIFIER NOT NULL,
    [AuthorName]     NVARCHAR (127)   NOT NULL,
    [PageId]         UNIQUEIDENTIFIER NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Content].[Blog] (
    [BlogId]         UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [BlogName]       NVARCHAR (127)   NOT NULL,
    [BlogOwnerId]    UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Content].[Wiki] (
    [WikiId]         UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [WikiName]       NVARCHAR (127)   NOT NULL,
    [WikiHomePageId] UNIQUEIDENTIFIER NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Content].[HtmlPageTag] (
    [HtmlPageTagId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [HtmlPageId]    UNIQUEIDENTIFIER NOT NULL,
    [TagId]         UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Content].[HtmlPage] (
    [HtmlPageId]     UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [PageId]         UNIQUEIDENTIFIER NOT NULL,
    [PageName]       NVARCHAR (127)   NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Content].[WikiPage] (
    [WikiPageId]     UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [WikiId]         UNIQUEIDENTIFIER NOT NULL,
    [PageId]         UNIQUEIDENTIFIER NOT NULL,
    [Title]          NVARCHAR (127)   NOT NULL,
    [ContentText]    NVARCHAR (MAX)   NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
ALTER TABLE [Content].[HtmlContent]
    ADD CONSTRAINT [PK_HtmlContent] PRIMARY KEY CLUSTERED ([ContentId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Content].[BlogAuthors]
    ADD CONSTRAINT [PK_BlogAuthors] PRIMARY KEY CLUSTERED ([BlogAuthorId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Content].[BlogPost]
    ADD CONSTRAINT [PK_BlogPost] PRIMARY KEY CLUSTERED ([BlogPostId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Content].[Blog]
    ADD CONSTRAINT [PK_Blog] PRIMARY KEY CLUSTERED ([BlogId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Content].[Wiki]
    ADD CONSTRAINT [PK_Wiki] PRIMARY KEY CLUSTERED ([WikiId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Content].[HtmlPageTag]
    ADD CONSTRAINT [PK_HtmlPageTag] PRIMARY KEY CLUSTERED ([HtmlPageTagId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Content].[HtmlPage]
    ADD CONSTRAINT [PK_HtmlPage] PRIMARY KEY CLUSTERED ([HtmlPageId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Content].[WikiPage]
    ADD CONSTRAINT [PK_WikiPage] PRIMARY KEY CLUSTERED ([WikiPageId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO






ALTER TABLE [Content].[HtmlContent]
    ADD CONSTRAINT [DF_HtmlContent_HtmlContentId] DEFAULT (newid()) FOR [ContentId];


GO
ALTER TABLE [Content].[HtmlContent]
    ADD CONSTRAINT [DF_HtmlContent_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Content].[HtmlContent]
    ADD CONSTRAINT [DF_HtmlContent_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Content].[HtmlContent]
    ADD CONSTRAINT [DF_HtmlContent_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Content].[BlogAuthors]
    ADD CONSTRAINT [FK_BlogAuthors_Blog] FOREIGN KEY ([BlogId]) REFERENCES [Content].[Blog] ([BlogId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_BlogAuthors]
    ON [Content].[BlogAuthors]([BlogId] ASC, [AuthorId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
ALTER TABLE [Content].[BlogPost]
    ADD CONSTRAINT [FK_BlogPost_Blog] FOREIGN KEY ([BlogId]) REFERENCES [Content].[Blog] ([BlogId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Content].[BlogPost]
    ADD CONSTRAINT [DF_BlogPost_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Content].[Blog]
    ADD CONSTRAINT [DF_Blog_BlogId] DEFAULT (newid()) FOR [BlogId];


GO
ALTER TABLE [Content].[Wiki]
    ADD CONSTRAINT [DF_Wiki_WikiId] DEFAULT (newid()) FOR [WikiId];


GO
ALTER TABLE [Content].[Wiki]
    ADD CONSTRAINT [DF_Wiki_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Content].[Wiki]
    ADD CONSTRAINT [DF_Wiki_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Content].[Wiki]
    ADD CONSTRAINT [DF_Wiki_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Content].[HtmlPageTag]
    ADD CONSTRAINT [FK_HtmlPageTag_HtmlPage] FOREIGN KEY ([HtmlPageId]) REFERENCES [Content].[HtmlPage] ([HtmlPageId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Content].[HtmlPageTag]
    ADD CONSTRAINT [DF_Table_1_HtmlContentTagId] DEFAULT (newid()) FOR [HtmlPageTagId];


GO
ALTER TABLE [Content].[HtmlPage]
    ADD CONSTRAINT [DF_HtmlPage_HtmlPageId] DEFAULT (newid()) FOR [HtmlPageId];


GO
ALTER TABLE [Content].[WikiPage]
    ADD CONSTRAINT [FK_WikiPage_Wiki] FOREIGN KEY ([WikiId]) REFERENCES [Content].[Wiki] ([WikiId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Content].[WikiPage]
    ADD CONSTRAINT [DF_WikiPage_WikiPageId] DEFAULT (newid()) FOR [WikiPageId];


GO
ALTER TABLE [Content].[WikiPage]
    ADD CONSTRAINT [DF_WikiPage_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO






CREATE SCHEMA [Core]
    AUTHORIZATION [dbo];


GO
CREATE TABLE [Core].[DBSchemaVer] (
    [Name]    NVARCHAR (127) NOT NULL,
    [Version] NCHAR (10)     NOT NULL
);


GO
CREATE TABLE [Core].[PermissionTags] (
    [PermissionId] UNIQUEIDENTIFIER NOT NULL,
    [TagId]        UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[Theme] (
    [ThemeId]           UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [Name]              NVARCHAR (127)   NOT NULL,
    [Layout]            NVARCHAR (127)   NOT NULL,
    [Width]             INT              NOT NULL,
    [ContentIdentifier] NVARCHAR (127)   NOT NULL,
    [SysMenuIdentifier] NVARCHAR (127)   NOT NULL,
    [MenuIdentifier]    NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[Address] (
    [AddressId]  UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [Line1]      NVARCHAR (127)   NULL,
    [Line2]      NVARCHAR (127)   NULL,
    [City]       NVARCHAR (127)   NOT NULL,
    [State]      NVARCHAR (25)    NULL,
    [PostalCode] NVARCHAR (10)    NULL,
    [Country]    NVARCHAR (127)   NULL
);


GO
CREATE TABLE [Core].[Domain] (
    [DomainId]             UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [DomainName]           NVARCHAR (127)   NOT NULL,
    [SiteTitle]            NVARCHAR (127)   NOT NULL,
    [IsMultiBUYN]          NCHAR (1)        NOT NULL,
    [SMTPFrom]             NVARCHAR (127)   NULL,
    [SMTPServer]           NVARCHAR (127)   NULL,
    [SMTPPort]             NVARCHAR (5)     NULL,
    [SMTPUseSSLYN]         NCHAR (1)        NULL,
    [SMTPAccount]          NVARCHAR (127)   NULL,
    [SMTPPassword]         NVARCHAR (127)   NULL,
    [SMTPAllowSendYN]      NCHAR (1)        NOT NULL,
    [EnableSelfRegisterYN] NCHAR (1)        NOT NULL,
    [ThemeId]              UNIQUEIDENTIFIER NOT NULL,
    [ReCaptchaPublicKey]   NVARCHAR (127)   NULL,
    [ReCaptchaPrivateKey]  NVARCHAR (127)   NULL,
    [PrimaryBU]            UNIQUEIDENTIFIER NOT NULL,
    [SubscriptionLevel]    NCHAR (1)        NOT NULL,
    [IsDroppedYN]          NCHAR (1)        NOT NULL,
    [CreateDT]             DATETIME         NOT NULL,
    [CreatedById]          UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]        NVARCHAR (127)   NOT NULL,
    [ModifiedDT]           DATETIME         NOT NULL,
    [ModifiedById]         UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName]       NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[FileTag] (
    [FileId] UNIQUEIDENTIFIER NOT NULL,
    [TagId]  UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[ContactBU] (
    [ContactId]      UNIQUEIDENTIFIER NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[File] (
    [FileId]         UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [DomainId]       UNIQUEIDENTIFIER NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [Description]    NVARCHAR (127)   NOT NULL,
    [Filename]       NVARCHAR (127)   NOT NULL,
    [Extension]      NVARCHAR (10)    NOT NULL,
    [Size]           INT              NOT NULL,
    [Data]           VARBINARY (MAX)  NOT NULL,
    [IsPublicYN]     NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [LastAccessedDT] DATETIME         NOT NULL
);


GO
CREATE TABLE [Core].[Company] (
    [CompanyId]      UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [CompanyName]    NVARCHAR (127)   NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[Contact] (
    [ContactId]       UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [FirstName]       NVARCHAR (50)    NOT NULL,
    [Middle]          NVARCHAR (50)    NULL,
    [LastName]        NVARCHAR (50)    NULL,
    [FullName]        AS               (([FirstName]+isnull(' '+[Middle],''))+isnull(' '+[LastName],'')),
    [Email]           NVARCHAR (127)   NOT NULL,
    [OtherEmail]      NVARCHAR (127)   NULL,
    [WorkPhone]       NVARCHAR (15)    NULL,
    [CellPhone]       NVARCHAR (15)    NULL,
    [HomePhone]       NVARCHAR (15)    NULL,
    [OtherPhone]      NVARCHAR (15)    NULL,
    [Status]          NCHAR (1)        NOT NULL,
    [EmailVerifiedYN] NCHAR (1)        NOT NULL,
    [IsDroppedYN]     NCHAR (1)        NOT NULL,
    [CreatedDT]       DATETIME         NOT NULL,
    [CreatedById]     UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]   NVARCHAR (127)   NOT NULL,
    [ModifiedDT]      DATETIME         NOT NULL,
    [ModifiedById]    UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName]  NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[CompanyContact] (
    [CompanyId] UNIQUEIDENTIFIER NOT NULL,
    [ContactId] UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[CompanyBU] (
    [CompanyId]      UNIQUEIDENTIFIER NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[BusinessUnit] (
    [BusinessUnitId]   UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [DomainId]         UNIQUEIDENTIFIER NOT NULL,
    [BusinessUnitName] NVARCHAR (127)   NOT NULL,
    [Phone]            NVARCHAR (15)    NOT NULL,
    [Fax]              NVARCHAR (15)    NULL,
    [Email]            NVARCHAR (127)   NOT NULL,
    [MainAddressId]    UNIQUEIDENTIFIER NOT NULL,
    [IsDroppedYN]      NCHAR (1)        NOT NULL,
    [CreateDT]         DATETIME         NOT NULL,
    [CreatedById]      UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]    NVARCHAR (127)   NOT NULL,
    [ModifiedDT]       DATETIME         NOT NULL,
    [ModifiedById]     UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName]   NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[Plugin] (
    [PluginId]          UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [Name]              NVARCHAR (127)   NOT NULL,
    [Version]           NVARCHAR (127)   NOT NULL,
    [DBVersionRequired] NVARCHAR (127)   NOT NULL,
    [Area]              NVARCHAR (127)   NULL,
    [Controller]        NVARCHAR (127)   NULL,
    [Action]            NVARCHAR (127)   NULL,
    [UserPermission]    NVARCHAR (127)   NULL,
    [AdminController]   NVARCHAR (127)   NULL,
    [AdminAction]       NVARCHAR (127)   NULL,
    [AdminPermission]   NVARCHAR (127)   NULL,
    [IsDroppedYN]       NCHAR (1)        NOT NULL
);


GO
CREATE TABLE [Core].[TagCompany] (
    [TagId]     UNIQUEIDENTIFIER NOT NULL,
    [CompanyId] UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[Role] (
    [RoleId]         UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [Name]           NVARCHAR (127)   NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[MainMenu] (
    [MenuItemId]     UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [DomainId]       UNIQUEIDENTIFIER NOT NULL,
    [Name]           NVARCHAR (15)    NOT NULL,
    [URL]            NVARCHAR (127)   NOT NULL,
    [UserPermission] NVARCHAR (127)   NOT NULL,
    [Sequence]       INT              NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[CompanyAddress] (
    [CompanyId] UNIQUEIDENTIFIER NOT NULL,
    [TagId]     UNIQUEIDENTIFIER NOT NULL,
    [AddressId] UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[Permission] (
    [PermissionId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [PluginId]     UNIQUEIDENTIFIER NOT NULL,
    [Name]         NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[ContactAddress] (
    [ContactId] UNIQUEIDENTIFIER NOT NULL,
    [TagId]     UNIQUEIDENTIFIER NOT NULL,
    [AddressId] UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[RolePermission] (
    [PermissionId] UNIQUEIDENTIFIER NOT NULL,
    [RoleId]       UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[Page] (
    [PageId]          UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [BusinessUnitId]  UNIQUEIDENTIFIER NOT NULL,
    [PermissionId]    UNIQUEIDENTIFIER NOT NULL,
    [PageName]        NVARCHAR (127)   NOT NULL,
    [Title]           NVARCHAR (127)   NOT NULL,
    [ShowTitleYN]     NCHAR (1)        NOT NULL,
    [MetaKeywords]    NVARCHAR (255)   NOT NULL,
    [MetaDescription] NVARCHAR (255)   NOT NULL,
    [ContainerId]     UNIQUEIDENTIFIER NOT NULL,
    [OnMainMenuYN]    NCHAR (1)        NOT NULL,
    [IsTemplateYN]    NCHAR (1)        NOT NULL,
    [IsDroppedYN]     NCHAR (1)        NOT NULL,
    [CreateDT]        DATETIME         NOT NULL,
    [CreatedById]     UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]   NVARCHAR (127)   NOT NULL,
    [ModifiedDT]      DATETIME         NOT NULL,
    [ModifiedById]    UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName]  NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[BUAddress] (
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [TagId]          UNIQUEIDENTIFIER NOT NULL,
    [AddressId]      UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[Authorization] (
    [AuthorizationId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [UserId]          UNIQUEIDENTIFIER NOT NULL,
    [RoleId]          UNIQUEIDENTIFIER NULL,
    [PermissionId]    UNIQUEIDENTIFIER NULL,
    [IsDroppedYN]     NCHAR (1)        NOT NULL,
    [CreateDT]        DATETIME         NOT NULL,
    [CreatedById]     UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]   NVARCHAR (127)   NOT NULL,
    [ModifiedDT]      DATETIME         NOT NULL,
    [ModifiedById]    UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName]  NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[User] (
    [UserId]         UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [ContactId]      UNIQUEIDENTIFIER NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[Message] (
    [MessageId]      UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [PluginId]       UNIQUEIDENTIFIER NOT NULL,
    [ContactId]      UNIQUEIDENTIFIER NOT NULL,
    [Subject]        NVARCHAR (127)   NOT NULL,
    [MessageText]    NVARCHAR (1000)  NOT NULL,
    [ReplyToId]      UNIQUEIDENTIFIER NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL
);


GO
CREATE TABLE [Core].[TagCategory] (
    [TagCategoryId]   UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [BusinessUnitId]  UNIQUEIDENTIFIER NULL,
    [TagCategoryName] NVARCHAR (127)   NOT NULL,
    [IsDroppedYN]     NCHAR (1)        NOT NULL,
    [CreateDT]        DATETIME         NOT NULL,
    [CreatedById]     UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]   NVARCHAR (127)   NOT NULL,
    [ModifiedDT]      DATETIME         NOT NULL,
    [ModifiedById]    UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName]  NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[ContactUser] (
    [ContactId]                              UNIQUEIDENTIFIER NOT NULL,
    [OpenIDUserName]                         NVARCHAR (255)   NULL,
    [Password]                               NVARCHAR (127)   NOT NULL,
    [Comment]                                NVARCHAR (255)   NULL,
    [SecurityQuestion]                       NVARCHAR (127)   NULL,
    [SecurityAnswer]                         NVARCHAR (127)   NULL,
    [InviteCode]                             NVARCHAR (10)    NULL,
    [InviteExpires]                          DATETIME         NULL,
    [IsDroppedYN]                            NCHAR (1)        NOT NULL,
    [IsApprovedYN]                           NCHAR (1)        NOT NULL,
    [IsOnLineYN]                             NCHAR (1)        NOT NULL,
    [IsLockedOutYN]                          NCHAR (1)        NOT NULL,
    [CreateDT]                               DATETIME         NOT NULL,
    [LastLoginDT]                            DATETIME         NULL,
    [LastActivityDT]                         DATETIME         NULL,
    [LastPasswordChangedDT]                  DATETIME         NULL,
    [LastLockedOutDT]                        DATETIME         NULL,
    [FailedPasswordAttemptCount]             INT              NOT NULL,
    [FailedPasswordAttemptWindowStart]       DATETIME         NULL,
    [FailedPasswordAnswerAttemptCount]       INT              NOT NULL,
    [FailedPasswordAnswerAttemptWindowStart] DATETIME         NULL
);


GO
CREATE TABLE [Core].[BUDomain] (
    [BusinessUnitId] UNIQUEIDENTIFIER NOT NULL,
    [DomainId]       UNIQUEIDENTIFIER NOT NULL
);


GO
CREATE TABLE [Core].[Tag] (
    [TagId]          UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [BusinessUnitId] UNIQUEIDENTIFIER NULL,
    [TagCategoryId]  UNIQUEIDENTIFIER NOT NULL,
    [TagName]        NVARCHAR (127)   NOT NULL,
    [IsDroppedYN]    NCHAR (1)        NOT NULL,
    [CreateDT]       DATETIME         NOT NULL,
    [CreatedById]    UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]  NVARCHAR (127)   NOT NULL,
    [ModifiedDT]     DATETIME         NOT NULL,
    [ModifiedById]   UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName] NVARCHAR (127)   NOT NULL
);


GO
CREATE TABLE [Core].[TemplateOveride] (
    [TemplateOverideId] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [PageId]            UNIQUEIDENTIFIER NOT NULL,
    [TemplateContentId] UNIQUEIDENTIFIER NOT NULL,
    [OverideContentId]  UNIQUEIDENTIFIER NOT NULL,
    [IsDroppedYN]       NCHAR (1)        NOT NULL,
    [ModifiedDT]        DATETIME         NOT NULL
);


GO
CREATE TABLE [Core].[Container] (
    [ContainerId]       UNIQUEIDENTIFIER NOT NULL,
    [ParentContainerId] UNIQUEIDENTIFIER NULL,
    [ContainerType]     NCHAR (1)        NOT NULL,
    [ContainerName]     NVARCHAR (127)   NOT NULL,
    [Title]             NVARCHAR (127)   NULL,
    [Width]             INT              NULL,
    [ShowChromeYN]      NCHAR (1)        NOT NULL,
    [PluginId]          UNIQUEIDENTIFIER NULL,
    [ContentId]         UNIQUEIDENTIFIER NULL,
    [NestLevel]         INT              NOT NULL,
    [Sequence]          INT              NOT NULL,
    [IsDroppedYN]       NCHAR (1)        NULL,
    [CreateDT]          DATETIME         NOT NULL,
    [CreatedById]       UNIQUEIDENTIFIER NOT NULL,
    [CreatedByName]     NVARCHAR (127)   NOT NULL,
    [ModifiedDT]        DATETIME         NOT NULL,
    [ModifiedById]      UNIQUEIDENTIFIER NOT NULL,
    [ModifiedByName]    NVARCHAR (127)   NOT NULL
);


GO

/* Create All Primary Keys */

ALTER TABLE [Core].[DBSchemaVer]
    ADD CONSTRAINT [PK_DBSchemaVer] PRIMARY KEY CLUSTERED ([Name] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[PermissionTags]
    ADD CONSTRAINT [PK_PermissionTags] PRIMARY KEY CLUSTERED ([PermissionId] ASC, [TagId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Address]
    ADD CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ([AddressId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Theme]
    ADD CONSTRAINT [PK_Theme] PRIMARY KEY CLUSTERED ([ThemeId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [PK_Domain] PRIMARY KEY CLUSTERED ([DomainId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[FileTag]
    ADD CONSTRAINT [PK_FileTag] PRIMARY KEY CLUSTERED ([FileId] ASC, [TagId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[ContactBU]
    ADD CONSTRAINT [PK_ContactBU] PRIMARY KEY CLUSTERED ([ContactId] ASC, [BusinessUnitId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[File]
    ADD CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED ([FileId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Company]
    ADD CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ([CompanyId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED ([ContactId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[CompanyContact]
    ADD CONSTRAINT [PK_CompanyContact] PRIMARY KEY CLUSTERED ([CompanyId] ASC, [ContactId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[CompanyBU]
    ADD CONSTRAINT [PK_CompanyBU] PRIMARY KEY CLUSTERED ([CompanyId] ASC, [BusinessUnitId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[BusinessUnit]
    ADD CONSTRAINT [PK_BusinessUnit] PRIMARY KEY CLUSTERED ([BusinessUnitId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Plugin]
    ADD CONSTRAINT [PK_Plugin] PRIMARY KEY CLUSTERED ([PluginId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[TagCompany]
    ADD CONSTRAINT [PK_TagCompany] PRIMARY KEY CLUSTERED ([TagId] ASC, [CompanyId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Role]
    ADD CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED ([RoleId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[MainMenu]
    ADD CONSTRAINT [PK_MainMenu] PRIMARY KEY CLUSTERED ([MenuItemId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[CompanyAddress]
    ADD CONSTRAINT [PK_CompanyAddress] PRIMARY KEY CLUSTERED ([CompanyId] ASC, [TagId] ASC, [AddressId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Permission]
    ADD CONSTRAINT [PK_Permission] PRIMARY KEY CLUSTERED ([PermissionId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[ContactAddress]
    ADD CONSTRAINT [PK_ContactAddress] PRIMARY KEY CLUSTERED ([ContactId] ASC, [TagId] ASC, [AddressId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[RolePermission]
    ADD CONSTRAINT [PK_RolePermission] PRIMARY KEY CLUSTERED ([PermissionId] ASC, [RoleId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [PK_Page] PRIMARY KEY CLUSTERED ([PageId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[BUAddress]
    ADD CONSTRAINT [PK_BUAddress] PRIMARY KEY CLUSTERED ([BusinessUnitId] ASC, [TagId] ASC, [AddressId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [PK_Authorization] PRIMARY KEY CLUSTERED ([AuthorizationId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[User]
    ADD CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([UserId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Message]
    ADD CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED ([MessageId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[TagCategory]
    ADD CONSTRAINT [PK_TagCategory] PRIMARY KEY CLUSTERED ([TagCategoryId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [PK_ContactUser_1] PRIMARY KEY CLUSTERED ([ContactId] ASC, [CreateDT] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[BUDomain]
    ADD CONSTRAINT [PK_BusinessUnitDomain] PRIMARY KEY CLUSTERED ([BusinessUnitId] ASC, [DomainId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Tag]
    ADD CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED ([TagId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[TemplateOveride]
    ADD CONSTRAINT [PK_TemplateOveride] PRIMARY KEY CLUSTERED ([TemplateOverideId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [PK_Container] PRIMARY KEY CLUSTERED ([ContainerId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO


/* Create All Foreign Keys and other depndencies */

ALTER TABLE [Core].[PermissionTags]
    ADD CONSTRAINT [FK_PermissionTags_Permission] FOREIGN KEY ([PermissionId]) REFERENCES [Core].[Permission] ([PermissionId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[PermissionTags]
    ADD CONSTRAINT [FK_PermissionTags_Tag] FOREIGN KEY ([TagId]) REFERENCES [Core].[Tag] ([TagId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Theme]
    ADD CONSTRAINT [DF_Theme_ThemeId] DEFAULT (newid()) FOR [ThemeId];


GO
ALTER TABLE [Core].[Theme]
    ADD CONSTRAINT [DF_Theme_Width] DEFAULT ((0)) FOR [Width];


GO
ALTER TABLE [Core].[Address]
    ADD CONSTRAINT [DF_Address_AddressId] DEFAULT (newid()) FOR [AddressId];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [FK_Domain_Theme] FOREIGN KEY ([ThemeId]) REFERENCES [Core].[Theme] ([ThemeId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_DomainId] DEFAULT (newid()) FOR [DomainId];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_SiteTitle] DEFAULT ('') FOR [SiteTitle];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_IsMultiBUYN] DEFAULT (N'N') FOR [IsMultiBUYN];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_SMTPAllowSendYN] DEFAULT (N'N') FOR [SMTPAllowSendYN];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_EnableSelfRegisterYN] DEFAULT (N'N') FOR [EnableSelfRegisterYN];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_ThemeId] DEFAULT ('{00000000-0000-0000-0000-000000000000}') FOR [ThemeId];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_SubscriptionLevel] DEFAULT (N'F') FOR [SubscriptionLevel];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [DF_Domain_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [CK_Domain_EnableSelfRegisterYN] CHECK ([EnableSelfRegisterYN]='N' OR [EnableSelfRegisterYN]='Y');


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [CK_Domain_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [CK_Domain_SMTPUseSSLYN] CHECK ([SMTPUseSSLYN]='N' OR [SMTPUseSSLYN]='Y');


GO
ALTER TABLE [Core].[Domain]
    ADD CONSTRAINT [CK_Domain_SubscriptionLevel] CHECK ([SubscriptionLevel]='E' OR [SubscriptionLevel]='B' OR [SubscriptionLevel]='P' OR [SubscriptionLevel]='F');


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Domain_Name]
    ON [Core].[Domain]([DomainName] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
ALTER TABLE [Core].[FileTag]
    ADD CONSTRAINT [FK_FileTag_Tag] FOREIGN KEY ([TagId]) REFERENCES [Core].[Tag] ([TagId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[FileTag]
    ADD CONSTRAINT [FK_FileTag_File] FOREIGN KEY ([FileId]) REFERENCES [Core].[File] ([FileId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[ContactBU]
    ADD CONSTRAINT [FK_ContactBU_Contact] FOREIGN KEY ([ContactId]) REFERENCES [Core].[Contact] ([ContactId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[ContactBU]
    ADD CONSTRAINT [FK_ContactBU_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
CREATE NONCLUSTERED INDEX [IX_ContactBU_Contact]
    ON [Core].[ContactBU]([ContactId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
CREATE NONCLUSTERED INDEX [IX_ContactBU_BusinessUnit]
    ON [Core].[ContactBU]([BusinessUnitId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
ALTER TABLE [Core].[File]
    ADD CONSTRAINT [FK_File_Domain] FOREIGN KEY ([DomainId]) REFERENCES [Core].[Domain] ([DomainId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[File]
    ADD CONSTRAINT [FK_File_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[File]
    ADD CONSTRAINT [DF_File_FileId] DEFAULT (newid()) FOR [FileId];


GO
ALTER TABLE [Core].[File]
    ADD CONSTRAINT [DF_File_IsPublicYN] DEFAULT (N'N') FOR [IsPublicYN];


GO
ALTER TABLE [Core].[File]
    ADD CONSTRAINT [DF_File_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[File]
    ADD CONSTRAINT [DF_File_LastAccessedDT] DEFAULT (getdate()) FOR [LastAccessedDT];


GO
ALTER TABLE [Core].[Company]
    ADD CONSTRAINT [DF_Company_CompanyId] DEFAULT (newid()) FOR [CompanyId];


GO
ALTER TABLE [Core].[Company]
    ADD CONSTRAINT [DF_Company_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Company]
    ADD CONSTRAINT [DF_Company_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[Company]
    ADD CONSTRAINT [DF_Company_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[Company]
    ADD CONSTRAINT [CK_Company_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [DF_Contact_EmailVerifiedYN] DEFAULT (N'N') FOR [EmailVerifiedYN];


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [DF_Contact_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [DF_Contact_CreatedDT] DEFAULT (getdate()) FOR [CreatedDT];


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [DF_Contact_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [DF_Contact_Status] DEFAULT (N'L') FOR [Status];


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [DF_Contact_ContactId] DEFAULT (newid()) FOR [ContactId];


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [CK_Contact_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [CK_Contact_Status] CHECK ([Status]='A' OR [Status]='C' OR [Status]='L');


GO
ALTER TABLE [Core].[Contact]
    ADD CONSTRAINT [CK_Contact_EmailVerifiedYN] CHECK ([EmailVerifiedYN]='N' OR [EmailVerifiedYN]='Y');


GO
ALTER TABLE [Core].[CompanyContact]
    ADD CONSTRAINT [FK_CompanyContact_Company] FOREIGN KEY ([CompanyId]) REFERENCES [Core].[Company] ([CompanyId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[CompanyContact]
    ADD CONSTRAINT [FK_CompanyContact_Contact] FOREIGN KEY ([ContactId]) REFERENCES [Core].[Contact] ([ContactId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[CompanyBU]
    ADD CONSTRAINT [FK_CompanyBU_Company] FOREIGN KEY ([CompanyId]) REFERENCES [Core].[Company] ([CompanyId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[CompanyBU]
    ADD CONSTRAINT [FK_CompanyBU_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[BusinessUnit]
    ADD CONSTRAINT [FK_BusinessUnit_Domain] FOREIGN KEY ([DomainId]) REFERENCES [Core].[Domain] ([DomainId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[BusinessUnit]
    ADD CONSTRAINT [FK_BusinessUnit_Address] FOREIGN KEY ([MainAddressId]) REFERENCES [Core].[Address] ([AddressId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[BusinessUnit]
    ADD CONSTRAINT [DF_BusinessUnit_BusinessUnitId] DEFAULT (newid()) FOR [BusinessUnitId];


GO
ALTER TABLE [Core].[BusinessUnit]
    ADD CONSTRAINT [DF_BusinessUnit_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[BusinessUnit]
    ADD CONSTRAINT [DF_BusinessUnit_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[BusinessUnit]
    ADD CONSTRAINT [DF_BusinessUnit_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[BusinessUnit]
    ADD CONSTRAINT [CK_BusinessUnit_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
CREATE NONCLUSTERED INDEX [IX_BusinessUnit_Name]
    ON [Core].[BusinessUnit]([BusinessUnitName] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
ALTER TABLE [Core].[Plugin]
    ADD CONSTRAINT [DF_Plugin_PluginId] DEFAULT (newid()) FOR [PluginId];


GO
ALTER TABLE [Core].[Plugin]
    ADD CONSTRAINT [DF_Plugin_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Plugin]
    ADD CONSTRAINT [CK_Plugin_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[TagCompany]
    ADD CONSTRAINT [FK_TagCompany_Company] FOREIGN KEY ([CompanyId]) REFERENCES [Core].[Company] ([CompanyId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[TagCompany]
    ADD CONSTRAINT [FK_TagCompany_Tag] FOREIGN KEY ([TagId]) REFERENCES [Core].[Tag] ([TagId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Role]
    ADD CONSTRAINT [DF_Role_RoleId] DEFAULT (newid()) FOR [RoleId];


GO
ALTER TABLE [Core].[Role]
    ADD CONSTRAINT [DF_Role_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Role]
    ADD CONSTRAINT [DF_Role_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[Role]
    ADD CONSTRAINT [DF_Role_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[Role]
    ADD CONSTRAINT [CK_Role_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[MainMenu]
    ADD CONSTRAINT [FK_MainMenu_Domain] FOREIGN KEY ([DomainId]) REFERENCES [Core].[Domain] ([DomainId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[MainMenu]
    ADD CONSTRAINT [DF_MainMenu_Sequence] DEFAULT ((0)) FOR [Sequence];


GO
ALTER TABLE [Core].[MainMenu]
    ADD CONSTRAINT [DF_MainMenu_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[MainMenu]
    ADD CONSTRAINT [DF_MainMenu_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[MainMenu]
    ADD CONSTRAINT [DF_MainMenu_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[MainMenu]
    ADD CONSTRAINT [DF_MainMenu_MenuItemId] DEFAULT (newid()) FOR [MenuItemId];


GO
ALTER TABLE [Core].[CompanyAddress]
    ADD CONSTRAINT [FK_CompanyAddress_Address] FOREIGN KEY ([AddressId]) REFERENCES [Core].[Address] ([AddressId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[CompanyAddress]
    ADD CONSTRAINT [FK_CompanyAddress_Company] FOREIGN KEY ([CompanyId]) REFERENCES [Core].[Company] ([CompanyId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[CompanyAddress]
    ADD CONSTRAINT [FK_CompanyAddress_Tag] FOREIGN KEY ([TagId]) REFERENCES [Core].[Tag] ([TagId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Permission]
    ADD CONSTRAINT [FK_Permission_Plugin] FOREIGN KEY ([PluginId]) REFERENCES [Core].[Plugin] ([PluginId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Permission]
    ADD CONSTRAINT [DF_Permission_PermissionId] DEFAULT (newid()) FOR [PermissionId];


GO
ALTER TABLE [Core].[ContactAddress]
    ADD CONSTRAINT [FK_ContactAddress_Address] FOREIGN KEY ([AddressId]) REFERENCES [Core].[Address] ([AddressId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[ContactAddress]
    ADD CONSTRAINT [FK_ContactAddress_Contact] FOREIGN KEY ([ContactId]) REFERENCES [Core].[Contact] ([ContactId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[ContactAddress]
    ADD CONSTRAINT [FK_ContactAddress_Tag] FOREIGN KEY ([TagId]) REFERENCES [Core].[Tag] ([TagId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[RolePermission]
    ADD CONSTRAINT [FK_RolePermission_Permission] FOREIGN KEY ([PermissionId]) REFERENCES [Core].[Permission] ([PermissionId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[RolePermission]
    ADD CONSTRAINT [FK_RolePermission_Role] FOREIGN KEY ([RoleId]) REFERENCES [Core].[Role] ([RoleId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [FK_Page_Container] FOREIGN KEY ([ContainerId]) REFERENCES [Core].[Container] ([ContainerId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [FK_Page_Permission] FOREIGN KEY ([PermissionId]) REFERENCES [Core].[Permission] ([PermissionId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [FK_Page_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [DF_Page_PageId] DEFAULT (newid()) FOR [PageId];


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [DF_Page_ShowTitleYN] DEFAULT (N'N') FOR [ShowTitleYN];


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [DF_Page_OnMainMenuYN] DEFAULT (N'N') FOR [OnMainMenuYN];


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [DF_Page_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [DF_Page_IsTemplateYN] DEFAULT (N'N') FOR [IsTemplateYN];


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [DF_Page_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[Page]
    ADD CONSTRAINT [DF_Page_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[BUAddress]
    ADD CONSTRAINT [FK_BUAddress_Address] FOREIGN KEY ([AddressId]) REFERENCES [Core].[Address] ([AddressId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[BUAddress]
    ADD CONSTRAINT [FK_BUAddress_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[BUAddress]
    ADD CONSTRAINT [FK_BUAddress_Tag] FOREIGN KEY ([TagId]) REFERENCES [Core].[Tag] ([TagId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [FK_Authorization_Role] FOREIGN KEY ([RoleId]) REFERENCES [Core].[Role] ([RoleId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [FK_Authorization_Permission] FOREIGN KEY ([PermissionId]) REFERENCES [Core].[Permission] ([PermissionId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [FK_Authorization_User] FOREIGN KEY ([UserId]) REFERENCES [Core].[User] ([UserId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [DF_Authorization_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [DF_Authorization_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [DF_Authorization_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [DF_Authorization_AuhtorizationId] DEFAULT (newid()) FOR [AuthorizationId];


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [CK_Authorization_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[Authorization]
    ADD CONSTRAINT [CK_Authorization_RoleOrPermisssion] CHECK ([RoleId] IS NOT NULL OR [PermissionId] IS NOT NULL);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Authorization]
    ON [Core].[Authorization]([UserId] ASC, [RoleId] ASC, [PermissionId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
ALTER TABLE [Core].[User]
    ADD CONSTRAINT [FK_User_Contact] FOREIGN KEY ([ContactId]) REFERENCES [Core].[Contact] ([ContactId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[User]
    ADD CONSTRAINT [FK_User_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[User]
    ADD CONSTRAINT [DF_User_UserId] DEFAULT (newid()) FOR [UserId];


GO
ALTER TABLE [Core].[User]
    ADD CONSTRAINT [DF_User_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[User]
    ADD CONSTRAINT [DF_User_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[User]
    ADD CONSTRAINT [DF_User_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[Message]
    ADD CONSTRAINT [FK_Message_Contact] FOREIGN KEY ([ContactId]) REFERENCES [Core].[Contact] ([ContactId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Message]
    ADD CONSTRAINT [FK_Message_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Message]
    ADD CONSTRAINT [DF_Message_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[Message]
    ADD CONSTRAINT [DF_Message_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Message]
    ADD CONSTRAINT [DF_Message_MessageId] DEFAULT (newid()) FOR [MessageId];


GO
ALTER TABLE [Core].[TagCategory]
    ADD CONSTRAINT [FK_TagCategory_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[TagCategory]
    ADD CONSTRAINT [DF_TagCategory_TagCategoryId] DEFAULT (newid()) FOR [TagCategoryId];


GO
ALTER TABLE [Core].[TagCategory]
    ADD CONSTRAINT [DF_TagCategory_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[TagCategory]
    ADD CONSTRAINT [DF_TagCategory_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[TagCategory]
    ADD CONSTRAINT [DF_TagCategory_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [FK_ContactUser_Contact] FOREIGN KEY ([ContactId]) REFERENCES [Core].[Contact] ([ContactId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [DF_ContactUser_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [DF_ContactUser_IsApprovedYN] DEFAULT (N'N') FOR [IsApprovedYN];


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [DF_ContactUser_IsOnLineYN] DEFAULT (N'N') FOR [IsOnLineYN];


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [DF_ContactUser_IsLockedOutYN] DEFAULT (N'N') FOR [IsLockedOutYN];


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [DF_ContactUser_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [DF_ContactUser_FailedPasswordAttemptCount] DEFAULT ((0)) FOR [FailedPasswordAttemptCount];


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [DF_ContactUser_FailedPasswordAnswerAttemptCount] DEFAULT ((0)) FOR [FailedPasswordAnswerAttemptCount];


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [CK_ContactUser_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [CK_ContactUser_IsApprovedYN] CHECK ([IsApprovedYN]='N' OR [IsApprovedYN]='Y');


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [CK_ContactUser_IsOnLineYN] CHECK ([IsOnLineYN]='N' OR [IsOnLineYN]='Y');


GO
ALTER TABLE [Core].[ContactUser]
    ADD CONSTRAINT [CK_ContactUser_IsLockedOutYN] CHECK ([IsLockedOutYN]='N' OR [IsLockedOutYN]='Y');


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_ContactUser]
    ON [Core].[ContactUser]([ContactId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
    ON [PRIMARY];


GO
ALTER TABLE [Core].[BUDomain]
    ADD CONSTRAINT [FK_BusinessUnitDomain_Domain] FOREIGN KEY ([DomainId]) REFERENCES [Core].[Domain] ([DomainId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[BUDomain]
    ADD CONSTRAINT [FK_BusinessUnitDomain_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Tag]
    ADD CONSTRAINT [FK_Tag_BusinessUnit] FOREIGN KEY ([BusinessUnitId]) REFERENCES [Core].[BusinessUnit] ([BusinessUnitId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Tag]
    ADD CONSTRAINT [FK_Tag_TagCategory] FOREIGN KEY ([TagCategoryId]) REFERENCES [Core].[TagCategory] ([TagCategoryId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Tag]
    ADD CONSTRAINT [DF_Tag_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[Tag]
    ADD CONSTRAINT [DF_Tag_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Tag]
    ADD CONSTRAINT [DF_Tag_TagId] DEFAULT (newid()) FOR [TagId];


GO
ALTER TABLE [Core].[Tag]
    ADD CONSTRAINT [CK_Tag_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[TemplateOveride]
    ADD CONSTRAINT [FK_TemplateOveride_Page] FOREIGN KEY ([PageId]) REFERENCES [Core].[Page] ([PageId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[TemplateOveride]
    ADD CONSTRAINT [DF_TemplateOveride_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[TemplateOveride]
    ADD CONSTRAINT [DF_TemplateOveride_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[TemplateOveride]
    ADD CONSTRAINT [DF_TemplateOveride_TemplateOverideId] DEFAULT (newid()) FOR [TemplateOverideId];


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [FK_Container_Plugin] FOREIGN KEY ([PluginId]) REFERENCES [Core].[Plugin] ([PluginId]) ON DELETE NO ACTION ON UPDATE NO ACTION;


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [DF_Container_ContainerType] DEFAULT (N'C') FOR [ContainerType];


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [DF_Container_ShowChrome] DEFAULT (N'N') FOR [ShowChromeYN];


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [DF_Container_NestLevel] DEFAULT ((0)) FOR [NestLevel];


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [DF_Container_Sequence] DEFAULT ((0)) FOR [Sequence];


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [DF_Container_IsDroppedYN] DEFAULT (N'N') FOR [IsDroppedYN];


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [DF_Container_CreateDT] DEFAULT (getdate()) FOR [CreateDT];


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [DF_Container_ModifiedDT] DEFAULT (getdate()) FOR [ModifiedDT];


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [CK_Container_ContainerType] CHECK ([ContainerType]='C' OR [ContainerType]='H' OR [ContainerType]='V');


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [CK_Container_IsDroppedYN] CHECK ([IsDroppedYN]='N' OR [IsDroppedYN]='Y');


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [CK_Container_PluginAndContentRequiredForContentTypeContainer] CHECK ([ContainerType]='C' AND [PluginId] IS NOT NULL AND [ContentId] IS NOT NULL);


GO
ALTER TABLE [Core].[Container]
    ADD CONSTRAINT [CK_Container_ShowChromeYN] CHECK ([ShowChromeYN]='N' OR [ShowChromeYN]='Y');


GO
